package com.dz.tool.Service.Impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.dz.tool.Dao.Master.MasterMysqlMapper;
import com.dz.tool.Dao.Slave.SlaveMysqlMapper;
import com.dz.tool.Dao.Slave.SlaveOracleMapper;
import com.dz.tool.Service.MysqlServer;
import com.dz.tool.Utils.ConnectionUtils;
import com.dz.tool.Utils.Excel.ExcelUtil;
import com.dz.tool.Utils.Txt.TXTUtils;
import com.dz.tool.Vo.MysqlField;
import com.dz.tool.Vo.MysqlTable;
import com.dz.tool.Vo.OracleField;
import com.dz.tool.Vo.OracleTable;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;

@Service
public class MysqlServerImpl implements MysqlServer {

    final
    MasterMysqlMapper masterMysqlMapper;
    final
    SlaveOracleMapper oracleMapper;
    final
    SlaveMysqlMapper slaveMysqlMapper;

    public MysqlServerImpl(SlaveOracleMapper oracleMapper, MasterMysqlMapper mysqlMapper, SlaveMysqlMapper slaveMysqlMapper) {
        this.oracleMapper = oracleMapper;
        this.masterMysqlMapper = mysqlMapper;
        this.slaveMysqlMapper = slaveMysqlMapper;
    }

    @Value("${spring.datasource.dynamic.datasource.db1.url}")
    private String MasterUrl;
    @Value("${spring.datasource.dynamic.datasource.db1.username}")
    private String MasterUsername;
    @Value("${spring.datasource.dynamic.datasource.db1.password}")
    private String MasterPassword;
    @Value("${spring.datasource.master.databaseName}")
    private String MasterDatabaseName;


    @Value("${spring.datasource.dynamic.datasource.db2.url}")
    private String SlaveUrl;
    @Value("${spring.datasource.dynamic.datasource.db2.username}")
    private String SlaveUsername;
    @Value("${spring.datasource.dynamic.datasource.db2.password}")
    private String SlavePassword;
    @Value("${spring.datasource.slave.databaseName}")
    private String SlaveDatabaseName;

    ///////////////////////////////////////////////////////////////////////////
    // mysql 针对数据库 生成字段Excel文档
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void MysqlToExcel(HttpServletResponse response, List<String> tableNames) {
        // 根据指定数据表获取结构
        // 根据结构拆除 表名 列名 数据类型 长度 注释
        // 导出Excel

        if (tableNames.size()<1) {
            // 获取所有表名
            tableNames = masterMysqlMapper.selectTableNameAll();
        }

        List<Map<String,Object>> endList = new ArrayList<>();
        // 初始表头
        Map<String,Object> startMap = new HashMap<>();
        startMap.put("tableName","表列表");
        // 表头数据
        List<Object> headOne = Arrays.asList("表名","表注释");
        // 将数据汇总
        List<List<Object>> startSheetDataList = new ArrayList<>();
        startSheetDataList.add(headOne);
        // 数据
        if (tableNames.size()>0) {
            Map<String, String> zhushi = new HashMap<>();
            List<Map<String, String>> maps = masterMysqlMapper.selectTableComment(MasterDatabaseName);
            if (maps.size()>0) {
                for (Map<String, String> map : maps) {
                    zhushi.put(map.get("TABLE_NAME"),map.get("TABLE_COMMENT"));
                }
            }
            for (String name : tableNames) {
                List<Object> mysqlFieldsList = new ArrayList<>();
                mysqlFieldsList.add(name);
                mysqlFieldsList.add(zhushi.get(name));
                startSheetDataList.add(mysqlFieldsList);
            }
        }
        startMap.put("sheetDataList",startSheetDataList);
        endList.add(startMap);
        int i = 1;
        for (String tableName : tableNames) {
            System.out.println(tableNames.size()+"/"+i+++"："+tableName);
            Map<String,Object> map = new HashMap<>();
            map.put("tableName",tableName);
            List<MysqlField> mysqlFields = masterMysqlMapper.selectTableNameOnCNAndTP(tableName,MasterDatabaseName);
            System.out.println(mysqlFields.size());
            // 表头数据
            List<Object> head = Arrays.asList("表名","列名","数据类型","长度","注释");
            // 将数据汇总
            List<List<Object>> sheetDataList = new ArrayList<>();
            sheetDataList.add(head);
            // 数据
            if (mysqlFields.size()>0) {
                for (MysqlField mysqlField : mysqlFields) {
                    List<Object> mysqlFieldsList = new ArrayList<>();
                    mysqlFieldsList.add(tableName);
                    mysqlFieldsList.add(mysqlField.getCOLUMN_NAME());
                    mysqlFieldsList.add(mysqlField.getDATA_TYPE());
                    if (StringUtils.isNotBlank(mysqlField.getCHARACTER_MAXIMUM_LENGTH())) {
                        if (mysqlField.getCHARACTER_MAXIMUM_LENGTH().length()>1 && !mysqlField.getCHARACTER_MAXIMUM_LENGTH().equals("null")) {
                            mysqlFieldsList.add(mysqlField.getCHARACTER_MAXIMUM_LENGTH());
                        }
                        else {
                            if (StringUtils.isNotBlank(mysqlField.getNUMERIC_PRECISION())) {
                                mysqlFieldsList.add(mysqlField.getNUMERIC_PRECISION()+","+mysqlField.getNUMERIC_SCALE());
                            }else {
                                mysqlFieldsList.add(" ");
                            }
                        }
                    }
                    else {
                        if (StringUtils.isNotBlank(mysqlField.getNUMERIC_PRECISION())) {
                            mysqlFieldsList.add(mysqlField.getNUMERIC_PRECISION()+","+mysqlField.getNUMERIC_SCALE());
                        }else {
                            mysqlFieldsList.add(" ");
                        }
                    }
                    mysqlFieldsList.add(mysqlField.getCOLUMN_COMMENT());
                    sheetDataList.add(mysqlFieldsList);
                }
            }
            map.put("sheetDataList",sheetDataList);
            endList.add(map);
        }

        // 导出数据
        ExcelUtil.exportAll(response,null,"dataBaseToolExcel", endList);

    }


    ///////////////////////////////////////////////////////////////////////////
    // Mysql 数据表对比
    // 用途：系统开发期间，多个环境，数据库有可能加字段了，但是你不知道具体加了什么，用这个脚本同步一下字段
    // 1.增加缺失字段
    // 2.增加缺失索引
    // 3.字段存在，类型长度不同
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void MysqlToField(List<String> tableList) throws IOException {
        SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        List<String> list = new ArrayList<>();
        if (tableList!=null && tableList.size()>0) {
            list.addAll(tableList);
        }
        else {
            tableList = masterMysqlMapper.selectTableNameAll();
            list.addAll(tableList);
        }

        List<MysqlTable> extable = new ArrayList<>();
        List<String> notfiled = new ArrayList<>();

        int count = list.size();
        int number = 1;

        for (String tableName : list) {

            System.out.println("=============================");
            System.out.println("开始时间：" + sdf.format(new Date()));
            System.out.println(number++ + "/" + count + " 数据表名：" + tableName + " 开始");

            String selectTable = slaveMysqlMapper.selectTable(tableName);
            if (StringUtils.isBlank(selectTable)) {
                System.out.println("此数据表不存在，直接创建！！！");
                String selectTableCreatSql = (String) masterMysqlMapper.selectTableSql(tableName).get("Create Table");
                try {
                    System.out.println(selectTableCreatSql);
                    slaveMysqlMapper.creatMysql(selectTableCreatSql);
                    System.out.println("创建表成功！！！");
                }catch (Exception e){
                    MysqlTable mysqlTable = new MysqlTable();
                    mysqlTable.setTableName(tableName);
                    mysqlTable.setExMessage("创建表失败 ：" + selectTableCreatSql);
                    extable.add(mysqlTable);
                    continue;
                }
            }
            else {
                System.out.println("数据表存在，开始获取字段");

                MysqlTable mysqlTableOne = selectMysql(tableName);
                if (mysqlTableOne.getFieldList().size() < 1) {
                    mysqlTableOne.setExMessage("原始数据库或取字段失败，可能是因为没有表的原因或者其他！！！");
                    extable.add(mysqlTableOne);
                    continue;
                }

                List<MysqlField> mysqlFieldsEnd = slaveMysqlMapper.selectTableNameOnCNAndTP(tableName,SlaveDatabaseName);
                System.out.println("目标表字段数量："+mysqlFieldsEnd.size());

                // 对比字段
                boolean exc = false;
                for (MysqlField fieldOne : mysqlTableOne.getFieldList()) {
                    boolean OK = false;
                    for (MysqlField fieldTwo : mysqlFieldsEnd) {
                        if (fieldOne.getCOLUMN_NAME().equals(fieldTwo.getCOLUMN_NAME())) {
                            System.out.println("字段："+fieldOne.getCOLUMN_NAME()+"在目标表中，不需要处理！！！");
                            OK = true;
                            // 字段有了，看看类型，长度是否一样
                            if (fieldOne.getCOLUMN_TYPE().equals(fieldTwo.getCOLUMN_TYPE())) {
                                System.out.println("类型长度一致，无需处理");
                            }
                            else {
                                notfiled.add("数据表："+tableName+"中字段"+fieldOne.getCOLUMN_NAME()+"类型不一致，原先是："+fieldTwo.getCOLUMN_TYPE()+"现在是："+fieldOne.getCOLUMN_TYPE());
                            }
                            break;
                        }
                    }
                    if (!OK) {
                        String tableSql = "ALTER TABLE "+tableName+" ADD COLUMN `"+fieldOne.getCOLUMN_NAME()+"` "+fieldOne.getCOLUMN_TYPE()+" NULL COMMENT '"+fieldOne.getCOLUMN_COMMENT()+"'";
                        System.out.println(tableSql);
                        try {
                            slaveMysqlMapper.creatMysql(tableSql);
                            System.out.println("创建字段成功！！！");
                        }catch (Exception e){
                            mysqlTableOne.setExMessage("创建字段失败 ：" + fieldOne.getCOLUMN_NAME());
                            extable.add(mysqlTableOne);
                            exc = true;
                            break;
                        }
                    }
                }
                if (exc) {
                    continue;
                }
                // 对比索引Key_name -> PRIMARY
                List<Map<String, Object>> mapListMaster = masterMysqlMapper.selectIndex(tableName);
                if (mapListMaster.size()>0) {

                    Map<String,String> MasterIndexMap = new HashMap<>();
                    for (Map<String, Object> map : mapListMaster) {
                        String keyName = String.valueOf(map.get("Key_name"));
                        if (keyName.equals("PRIMARY")) {
                            continue;
                        }
                        String s = MasterIndexMap.get(keyName);
                        if (StringUtils.isNotBlank(s)){
                            s = s + "," + map.get("Column_name");
                            MasterIndexMap.put(keyName,s);
                        }
                        else {
                            MasterIndexMap.put(keyName,String.valueOf(map.get("Column_name")));
                        }
                    }
                    System.out.println("主表索引数量："+MasterIndexMap.size());

                    List<Map<String, Object>> mapListSlave = slaveMysqlMapper.selectIndex(tableName);
                    Map<String,String> SlaveIndexMap = new HashMap<>();
                    if (mapListSlave!=null&&mapListSlave.size()>0) {
                        for (Map<String, Object> map : mapListSlave) {
                            String keyName = String.valueOf(map.get("Key_name"));
                            if (keyName.equals("PRIMARY")) {
                                continue;
                            }
                            String s = SlaveIndexMap.get(keyName);
                            if (StringUtils.isNotBlank(s)){
                                s = s + "," + map.get("Column_name");
                                SlaveIndexMap.put(keyName,s);
                            }
                            else {
                                SlaveIndexMap.put(keyName,String.valueOf(map.get("Column_name")));
                            }
                        }
                    }

                    if (MasterIndexMap.entrySet().size()>0) {
                        for (Map.Entry<String, String> stringStringEntry : MasterIndexMap.entrySet()) {
                            if (StringUtils.isNotBlank(SlaveIndexMap.get(stringStringEntry.getKey()))) {
                                System.out.println(stringStringEntry.getKey()+"索引存在！！");
                            }
                            else {
                                String indexSql = "CREATE INDEX "+stringStringEntry.getKey()+" ON "+tableName+" (";
                                for (String s : stringStringEntry.getValue().split(",")) {
                                    indexSql = indexSql + s + ",";
                                }
                                indexSql = indexSql.substring(0,indexSql.length()-1);
                                indexSql = indexSql + ")";
                                System.out.println(indexSql);
                                slaveMysqlMapper.creatMysql(indexSql);
                                System.out.println("索引创建成功！！");
                            }
                        }
                    }
                    else {
                        System.out.println("没有索引，跳过创建！！！");
                    }
                }
            }
            System.out.println("数据表名：" + tableName + " 结束");
            System.out.println("结束时间："+ sdf.format(new Date()));
            System.out.println("=============================");
        }

        System.out.println("=============================");
        if (notfiled.size()>0) {
            for (String s : notfiled) {
                System.out.println(s);
            }
        }
        System.out.println("对比完成，失败数为：" + extable.size());

        if (extable.size()>0) {
            System.out.println("以下失败表名");
            StringBuffer stringBuffer = new StringBuffer();
            for (MysqlTable s : extable) {
                System.out.println(s.getTableName() + "原因是：" + s.getExMessage());
                stringBuffer.append(s.getTableName() + "原因是：" + s.getExMessage());
            }
            TXTUtils.creatTxt("exception",stringBuffer.toString());
        }

    }


    ///////////////////////////////////////////////////////////////////////////
    // Mysql 转 Mysql
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void MysqlToMysql(List<String> tableList,boolean dataBool) throws Exception {

        // 获取所有表名
        if (tableList == null || tableList.size()<1) {
            tableList = masterMysqlMapper.selectTableNameAll();
        }

        if(tableList.size()<1){
            System.out.println("库中没有表，迁移结束！！");
            return;
        }

        int size = tableList.size();
        int i = 1;
        List<MysqlTable> extable = new ArrayList<>();

        for (String tableName : tableList) {

            System.out.println("=============================");
            System.out.println(i++ + "/" + size + " 数据表名：" + tableName + " 开始");

            // 根据表名获取字段列表
            MysqlTable mysqlTable = selectMysql(tableName);

            if (mysqlTable.getFieldList().size()<1) {
                mysqlTable.setExMessage("此数据表无字段，跳过创建");
                extable.add(mysqlTable);
                continue;
            }

            // 根据表名获取创建语句
            Map<String, Object> tableSql = masterMysqlMapper.selectTableSql(mysqlTable.getTableName());
            mysqlTable.setTableSql((String) tableSql.get("Create Table"));


            // 创建表会删除同名旧表
//            try {
//                slaveMysqlMapper.creatMysql("drop table " +mysqlTable.getTableName());
//            }catch (Exception r){
//
//            }

            // 执行创建表
            try {
                slaveMysqlMapper.creatMysql(mysqlTable.getTableSql());
            }catch (Exception e){
                mysqlTable.setExMessage("创建失败 ：" + mysqlTable.getTableSql());
                System.out.println(mysqlTable.getExMessage());
                extable.add(mysqlTable);
                continue;
            }

            if (dataBool) {

                // 开始获取相关表的数据
                List<Map<String, Object>> dataList = null;
                try{
                    dataList = masterMysqlMapper.selectData(mysqlTable.getTableName());
                    mysqlTable.getDataList().addAll(dataList);
                }catch (Exception e){
                    mysqlTable.setExMessage("读取数据失败，此问题出现概率低，目前发现可能性有：1.数据获取代码存在问题。 2.当前表不属于当前用户下。");
                    extable.add(mysqlTable);
                    continue;
                }

                // 转成对应insertSQl

                // 执行数据存储语句

            }


            System.out.println("数据表名：" + tableName + " 结束");
            System.out.println("=============================");
        }

        System.out.println("迁移完成，失败数为：" + extable.size());

        if (extable.size()>0) {
            System.out.println("以下失败表名");
            for (MysqlTable s : extable) {
                System.out.println(s.getTableName() + "原因是：" + s.getExMessage());
            }
        }

    }


    ///////////////////////////////////////////////////////////////////////////
    // Mysql 根据提供的sql迁移到指定数据表
    // todo sqlPor 需要执行的具体sql语句，但是得加一个东西，举例 select 字段A,字段B UNION ALL select A,B from 表名 where 条件 查询前面手动加一行标题数据，用来确认字段对标
    // todo tableName 目标表名
    ///////////////////////////////////////////////////////////////////////////
    public void MysqlToSqlTOTable(String sqlPor,String tableName){

        int insertNum = 1; // 开始获取相关表的数据
        int threadCount = 5; // 线程数量

        List<Map<String, Object>> dataList = null;
        Integer selectDataCount = 200000000;//masterMysqlMapper.selectSqlCount(sqlPor);
        System.out.println("数据量为：" + selectDataCount);

        System.out.println("批量存储");
        final String tableNameEndFinal = tableName;

        StringBuffer exMessage = new StringBuffer();

        if (selectDataCount.intValue()>0) {
            //获取表对应结构
            MysqlTable mysqlTable= new MysqlTable();
            mysqlTable.setTableName(tableName);
            List<MysqlField> mysqlFields = slaveMysqlMapper.selectTableNameOnCNAndTP(tableName,SlaveDatabaseName);
            mysqlTable.setFieldList(mysqlFields);
            System.out.println("字段数量："+mysqlFields.size());

            if (mysqlFields.size()>0) {
                BigDecimal one = new BigDecimal(selectDataCount);
                BigDecimal two = new BigDecimal(insertNum);
                BigDecimal divide = one.divide(two, RoundingMode.HALF_UP);
                divide = divide.add(new BigDecimal(1));
                System.out.println("单次数量为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);
                divide = divide.divide(new BigDecimal(threadCount), RoundingMode.HALF_UP);
                divide = divide.add(new BigDecimal(1));
                System.out.println("除以线程次数一共为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);

                final boolean[] OK = {true,true,true,true,true};
                final int[] start = {0};
                final int[] end = {start[0]+insertNum};
                for (Integer i = 1; i <= Integer.valueOf(String.valueOf(divide.toString())); i++) {

                    try {
                        CountDownLatch latch = new CountDownLatch(threadCount);

//                        String sql = sqlPor + " LIMIT "+end[0]+" offset "+start[0];
//                        String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];

                        if (mysqlTable.getFieldList().size()>0) {
                            String sql = sqlPor + " WHERE B.id>=1 AND B.id<=1";
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            List<MysqlField> fieldList = new ArrayList<>();
                            // 形成添加语句
                            Map<String, Object> dataMap = dataListFinal.get(0);
                            for (Map.Entry<String, Object> stringObjectEntry : dataMap.entrySet()) {
                                for (MysqlField field : mysqlTable.getFieldList()) {
                                    if (field.getCOLUMN_NAME().equals(stringObjectEntry.getKey())) {
                                        fieldList.add(field);
                                        break;
                                    }
                                }
                            }
                            mysqlTable.setFieldList(fieldList);
                            fieldList = null;
                            dataListFinal = null;
                        }

                        List<Map<String, Object>> dataListFinalAll = new ArrayList<>();

                        Thread thread = new Thread(()->{
                            String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            dataListFinalAll.addAll(dataListFinal);
                            dataListFinal = null;
                            latch.countDown();
                        });
                        thread.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread2 = new Thread(()->{
                            String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            dataListFinalAll.addAll(dataListFinal);
                            dataListFinal = null;
                            latch.countDown();
                        });
                        thread2.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread3 = new Thread(()->{
                            String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            dataListFinalAll.addAll(dataListFinal);
                            dataListFinal = null;
                            latch.countDown();
                        });
                        thread3.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread4 = new Thread(()->{
                            String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            dataListFinalAll.addAll(dataListFinal);
                            dataListFinal = null;
                            latch.countDown();
                        });
                        thread4.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread5 = new Thread(()->{
                            String sql = sqlPor + " WHERE B.id>="+start[0]+" AND B.id<="+end[0];
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            List<Map<String, Object>> dataListFinal = masterMysqlMapper.selectMysqlData(sql);
                            dataListFinalAll.addAll(dataListFinal);
                            dataListFinal = null;
                            latch.countDown();
                        });
                        thread5.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        try {
                            // 等待所有线程执行完毕
                            latch.await();
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                        System.out.println("等待结束");

                        boolean inseBigData = inseBigData(dataListFinalAll, mysqlTable, exMessage);
                        if (!inseBigData) {
                            break;
                        }

                    }catch (Exception e){
                        e.printStackTrace();
                        break;
                    }
                }
            }
        }

        System.out.println("迁移完成");
        if (StringUtils.isNotBlank(exMessage.toString())) {
            System.out.println("错误信息日志输出");
            TXTUtils.creatTxt("exception",exMessage.toString());
        }

    }

    ///////////////////////////////////////////////////////////////////////////
    // Mysql 批量执行存过
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void MysqlToSaved(String savedName,int type,String years) {

        int insertNum = 50000; // 开始获取相关表的数据
        int threadCount = 5; // 线程数量

        List<Map<String, Object>> dataList = null;
        Integer selectDataCount = 55000000;//masterMysqlMapper.selectSqlCount(sqlPor);

        if (type==1) {
            String sql = "CALL "+savedName+"()";
            System.out.println(sql);
            masterMysqlMapper.creatMysql(sql);
            System.out.println("执行结束");
        }
        else {
            if (selectDataCount.intValue()>0) {
                BigDecimal one = new BigDecimal(selectDataCount);
                BigDecimal two = new BigDecimal(insertNum);
                BigDecimal divide = one.divide(two, RoundingMode.HALF_UP);
                divide = divide.add(new BigDecimal(1));
                System.out.println("单次数量为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);
                divide = divide.divide(new BigDecimal(threadCount), RoundingMode.HALF_UP);
                divide = divide.add(new BigDecimal(1));
                System.out.println("除以线程次数一共为(可能出现次数比你们算的多1,2次，不会少，原因计算逻辑有问题，懒得改，凑乎用吧，否则自己搞去)：" + divide);

                final boolean[] OK = {true,true,true,true,true};
                final int[] start = {0}; // 10250001 使用完替换成 0
                final int[] end = {start[0]+insertNum};  // 10250000 使用完替换成 start[0]
                for (Integer i = 1; i <= Integer.valueOf(String.valueOf(divide.toString())); i++) {
                    try {

                        CountDownLatch latch = new CountDownLatch(threadCount);
                        Thread thread = new Thread(()->{
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            String sql = null;
                            if (StringUtils.isNotBlank(years)) {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+","+years+")";
                            }
                            else {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+")";
                            }
                            System.out.println(sql);
                            masterMysqlMapper.creatMysql(sql);
                            latch.countDown();
                        });
                        thread.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread2 = new Thread(()->{
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            String sql = null;
                            if (StringUtils.isNotBlank(years)) {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+","+years+")";
                            }
                            else {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+")";
                            }
                            System.out.println(sql);
                            masterMysqlMapper.creatMysql(sql);
                            latch.countDown();
                        });
                        thread2.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread3 = new Thread(()->{
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            String sql = null;
                            if (StringUtils.isNotBlank(years)) {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+","+years+")";
                            }
                            else {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+")";
                            }
                            System.out.println(sql);
                            masterMysqlMapper.creatMysql(sql);
                            latch.countDown();
                        });
                        thread3.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread4 = new Thread(()->{
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            String sql = null;
                            if (StringUtils.isNotBlank(years)) {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+","+years+")";
                            }
                            else {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+")";
                            }
                            System.out.println(sql);
                            masterMysqlMapper.creatMysql(sql);
                            latch.countDown();
                        });
                        thread4.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        Thread thread5 = new Thread(()->{
                            System.out.println("开始："+ start[0] + " 结束：" + end[0]);
                            String sql = null;
                            if (StringUtils.isNotBlank(years)) {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+","+years+")";
                            }
                            else {
                                sql = "CALL "+savedName+"("+start[0]+","+ end[0]+")";
                            }
                            System.out.println(sql);
                            masterMysqlMapper.creatMysql(sql);
                            latch.countDown();
                        });
                        thread5.start();
                        Thread.sleep(2000);
                        start[0] = end[0] + 1;
                        end[0] = end[0] + insertNum;

                        try {
                            // 等待所有线程执行完毕
                            latch.await();
                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }
                        System.out.println("执行结束");

                    }catch (Exception e){
                        e.printStackTrace();
                        break;
                    }
                }
            }
        }

        System.out.println("迁移完成");
    }


    ///////////////////////////////////////////////////////////////////////////
    // Mysql 转 Oracle
    ///////////////////////////////////////////////////////////////////////////
    @Override
    public void MysqlToOracle() {

        // 1.获取所有表名
        List<String> list = masterMysqlMapper.selectTableNameAll();

        int size = list.size();
        int i = 1;
        List<MysqlTable> extable = new ArrayList<>();

        for (String tableName : list) {

            System.out.println("=============================");
            System.out.println(i++ + "/" + size + " 数据表名：" + tableName + " 开始");

            MysqlTable mysqlTable = selectMysql(tableName);

            if (mysqlTable.getFieldList().size()<1) {
                mysqlTable.setExMessage("此数据表无字段，跳过创建");
                extable.add(mysqlTable);
                continue;
            }

            // 4.生成创建Oracle数据表语句



            // 生成前先删除旧数据
            try {
                oracleMapper.creatOracle("drop table " + mysqlTable.getTableName());
            }catch (Exception e){

            }

            // 执行创建表
            try {
                masterMysqlMapper.creatMysql(mysqlTable.getTableSql());
            }catch (Exception e){
                mysqlTable.setExMessage("创建失败 ：" + mysqlTable.getTableSql());
                extable.add(mysqlTable);
                continue;
            }

        }

        System.out.println("迁移完成，失败数为：" + extable.size());

        if (extable.size()>0) {
            System.out.println("以下失败表名");
            for (MysqlTable s : extable) {
                System.out.println(s.getTableName() + "原因是：" + s.getExMessage());
            }
        }

    }

    ///////////////////////////////////////////////////////////////////////////
    // Mysql 根据驼峰数据集合，对比指定表字段，看对应的注释
    // todo 传参方式 tableFieldMap.put("web_clm_accdnt","CPkId,CInsAccPkId");
    ///////////////////////////////////////////////////////////////////////////
    public void MysqlToFieldComment(Map<String,String> tableFieldMap){
        try {

            if (tableFieldMap.size()<1) {
                System.out.println("传参无数据");
                return;
            }

            for (Map.Entry<String, String> oneMap : tableFieldMap.entrySet()) {
                String tableName = oneMap.getKey();
                String filedList = oneMap.getValue();
                // 查询数据表结构
                List<MysqlField> mysqlFields = masterMysqlMapper.selectTableNameOnCNAndTP(tableName, MasterDatabaseName);
                // 对比字段查看中文
                String[] fileSplit = filedList.split(",");
                for (String filef : fileSplit) {
                    boolean ok = false;
                    String snakeCase = camelToSnake(filef);
                    for (MysqlField mysqlField : mysqlFields) {
//                        System.out.println("输入值:"+snakeCase+" 匹配值："+mysqlField.getCOLUMN_NAME());
                        if (snakeCase.equalsIgnoreCase(mysqlField.getCOLUMN_NAME())) {
                            ok=true;
                            System.out.println("表名：" + tableName + "字段名：accdnt." + filef + "中文：" + mysqlField.getCOLUMN_COMMENT());
                        }
                    }
                    if(!ok){
                        System.out.println("表名：" + tableName + "字段名：" + filef + "找不到");
                    }
                }


            }

            System.out.println("功能结束");

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static String camelToSnake(String camelCase) {
        StringBuilder snakeCase = new StringBuilder();
        for (int i = 0; i < camelCase.length(); i++) {
            char c = camelCase.charAt(i);
            if (Character.isUpperCase(c) && i > 0) {
                snakeCase.append("_");
            }
            snakeCase.append(c);
        }
        return snakeCase.toString();
    }

    ///////////////////////////////////////////////////////////////////////////
    // todo 其他
    ///////////////////////////////////////////////////////////////////////////

    //根据表名获取表结构
    public MysqlTable selectMysql(String tableName){
        MysqlTable mysqlTable= new MysqlTable();
        mysqlTable.setTableName(tableName);
        // 3.获取字段
        List<MysqlField> mysqlFields = masterMysqlMapper.selectTableNameOnCNAndTP(tableName,MasterDatabaseName);
        mysqlTable.setFieldList(mysqlFields);
        System.out.println("字段数量："+mysqlFields.size());
        return mysqlTable;
    }

    public void creatTableSql(MysqlTable mysqlTable){

        StringBuffer buffer = new StringBuffer();


    }

    // 表数据存储
    public boolean inseBigData(List<Map<String, Object>> dataList, MysqlTable mysqlTable, StringBuffer exMessage) {
        System.out.println("数据存储量：" + dataList.size());
        if (dataList.size()>0) {
            System.out.println("开始存储");
            mysqlTable.setDataList(dataList);
            String creatInsert = creatInsertMysql(mysqlTable);
            // 执行存储数据
            try {
                slaveMysqlMapper.creatMysql(creatInsert);
            }catch (Exception e){
                System.out.println("存储方法一失败！！！执行方法2号");
                e.printStackTrace();
                try {
                    ConnectionUtils connectionUtils = new ConnectionUtils();
                    connectionUtils.sqlConnection(creatInsert,SlaveUrl,SlaveUsername,SlavePassword);
                }catch (Exception e2){
                    exMessage.append("数据存储二失败："+e.getMessage()+",insert语句为："+creatInsert+",存储失败，猜测原因：1.语句存在问题。2.数据表并非本程序创建，跟原始表可能存在差异导致!!");
                    System.out.println(e.getMessage());
                    return false;
                }
            }
        }
        return true;
    }

    // 形成添加语句 （Mysql）
    public String creatInsertMysql(MysqlTable mysqlTable){

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer
                .append("insert into " + mysqlTable.getTableName() + "(");

        int y = 0;
        for (MysqlField mysqlField : mysqlTable.getFieldList()) {
            if (y!=0) {
                stringBuffer.append(",");
            }
            ++y;
            stringBuffer.append(mysqlField.getCOLUMN_NAME());
        }
        y = 0;

        stringBuffer.append(") values ");

        for (Map<String, Object> stringObjectMap : mysqlTable.getDataList()) {

            if (y!=0) {
                stringBuffer.append(",");
            }
            ++y;

            stringBuffer.append("(");

            int a = 0;
            for (MysqlField mysqlField : mysqlTable.getFieldList()) {
                if (a!=0) {
                    stringBuffer.append(",");
                }
                ++a;
                String filedTo = filedToMysql(mysqlField, String.valueOf(stringObjectMap.get(mysqlField.getCOLUMN_NAME())));
                if (StringUtils.isBlank(filedTo)) {
                    filedTo = "";
                }
                stringBuffer.append(filedTo);
            }

            stringBuffer.append(")");

        }

        return stringBuffer.toString();

    }

    // 对存储的数据加一层过滤,处理特殊符号
    public String specialSymbol(String name){

        if (name.contains("'")) {
            name = name.replaceAll("'","\"");
        }

        if (name.contains("\\")) {
            name = name.replaceAll("\\\\", "\\\\\\\\");
        }

        return name;
    }

    // 处理mysql字段
    public String filedToMysql(MysqlField mysqlField,String name){

        // 存储数据过滤
        name = specialSymbol(name);

        String data_type = mysqlField.getDATA_TYPE();

        if (data_type.equals("text") || data_type.contains("varchar(") || data_type.equals("varchar") ||
                data_type.equals("longblob")  || data_type.equals("char")
        ) {

            if (name.equals("null")) {
                return "''";
            }

            return "'" + name +"'";

        }

        if (data_type.equals("timestamp(6)") || data_type.equals("timestamp")) {

            if (name.equals("null")) {
                return "null";
            }
            return "'" + name +"'";
        }


        if (data_type.equals("date") || data_type.equals("datetime(6)") || data_type.equals("datetime")) {
            if (name.equals("null")) {
                return "NULL";
            }
            if (name.equals("0000")) {
                System.out.println(name);
            }
            return "'" + name + "'";
        }


        if (data_type.contains("integer(")
                || data_type.contains("decimal(")
                || data_type.equals("decimal")
                || data_type.equals("double")
                || data_type.equals("bigint")
                || data_type.equals("int")
        ) {

            return name;

        }

        return null;
    }



}
